package com.learn;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DBConnection {
	String url = "jdbc:mysql://127.0.0.1:3306/communitygui?user=root&characterEncoding=utf8&useSSL=false";
	String username = "root";
	String password = "root";

	Connection con = null;
	Statement stmt;
	PreparedStatement prestmt;
	ResultSet rs;
	String sql;

	public DBConnection() {
		try {// 1,加载驱动器类
			Class.forName("com.mysql.jdbc.Driver");
			// 2,获得数据库连接对象
			con = DriverManager.getConnection(url, username, password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public Connection getCon() {
		return con;
	}

	public boolean findByNumber(String number) {
		try {// 3,得到Statement语句对象
				// stmt=con.createStatement();
				// 4，执行增删改查操作
			sql = "select * from student where number=?";
			prestmt = con.prepareStatement(sql);
			prestmt.setString(1, number);
			rs = prestmt.executeQuery(); // rs.next()
			if (rs != null && rs.next())
				return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean insertRecord(String[] values) {
		try {
			// stmt=con.createStatement();
			sql = "insert into student(number,name,clazz,major,commu,position) values(?,?,?,?,?,?)";
			prestmt = con.prepareStatement(sql);
			/*
			 * StringBuffer sBuffer=new StringBuffer(sql); for(int
			 * i=0;i<values.length-1;i++){
			 * sBuffer.append("'").append(values[i]).append("',"); }
			 * sBuffer.append("'").append(values[values.length-1]).append("')");
			 * sql=sBuffer.toString();
			 */
			for (int i = 0; i < values.length; i++)
				prestmt.setString(i + 1, values[i]);

			if (prestmt.executeUpdate() == 1)
				return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean updateRecord(String[] values) {
		try {
			// stmt=con.createStatement();
			sql = "update student set name=?,clazz=?,major=?,commu=?,position=? where number=?;";
			prestmt = con.prepareStatement(sql);
			/*
			 * StringBuffer sBuffer=new StringBuffer(sql); for(int
			 * i=0;i<values.length-1;i++){
			 * sBuffer.append("'").append(values[i]).append("',"); }
			 * sBuffer.append("'").append(values[values.length-1]).append("')");
			 * sql=sBuffer.toString();
			 */
			for (int i = 0; i < values.length; i++)
				prestmt.setString(i + 1, values[i]);
			if (prestmt.executeUpdate() == 1)
				return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean queryRecord(String number) throws SQLException {
		String sql = "select number from student where number = ? ;";
		prestmt = con.prepareStatement(sql);
		prestmt.setString(1, number);
		ResultSet rs = prestmt.executeQuery();
		while (rs.next()) {
			return true;
		}
		return false;
	}

	public String[] queryInfo(String number) throws SQLException {
		String sql = "select * from student where number = ? ;";
		prestmt = con.prepareStatement(sql);
		prestmt.setString(1, number);
		ResultSet rs = prestmt.executeQuery();
		String name = null;
		String clazz = null;
		String major = null;
		String commu = null;
		String position = null;
		while (rs.next()) {
			name = rs.getString("name");
			clazz = rs.getString("clazz");
			major = rs.getString("major");
			commu = rs.getString("commu");
			position = rs.getString("position");
		}
		if (name == null) {
			return null;
		}
		String[] values = { name, clazz, major, commu, position };
		return values;
	}

	public boolean delete(String number) throws SQLException {
		String sql = "delete from student where number= ? ;";
		prestmt = con.prepareStatement(sql);
		prestmt.setString(1, number);
		prestmt.executeUpdate();
		return true;
	}

	public void close() {// 在不需要的时候，把系统资源释放掉
		try {
			if (rs != null)
				rs.close();
			if (stmt != null)
				stmt.close();
			if (con != null)
				con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public List<String[]> queryAllInfo() throws SQLException {
		String sql = "select * from student;";
		prestmt = con.prepareStatement(sql);
		ResultSet rs = prestmt.executeQuery();
		List<String[]> values = new ArrayList<String[]>();
		while (rs.next()) {
			String number = rs.getString("number");
			String name = rs.getString("name");
			String clazz = rs.getString("clazz");
			String major = rs.getString("major");
			String commu = rs.getString("commu");
			String position = rs.getString("position");
			String[] student = { number, name, clazz, major, commu, position };
			values.add(student);
		}
		return values;
	}

}
